--1 Lay toan bo phim trong csdl
CREATE PROC SP_GETALLFILM
AS
BEGIN
SELECT F.MaPhim, F.TenPhim, F.NoiDung, LF.TenLoaiPhim, F.DaoDien, F.DienVien, F.NuocSanXuat, F.HinhAnh
FROM PHIM F, LOAIPHIM LF
WHERE F.MaLoaiPhim = LF.MaLoaiPhim
END
GO

--2 Lay nhung phim dang chieu
CREATE PROC SP_GETALLFILMSHOWING
AS
BEGIN
DECLARE @NOW DATETIME
SET @NOW = GETDATE()
print @NOW
SELECT DISTINCT F.MaPhim, F.TenPhim, F.NoiDung, LF.TenLoaiPhim, F.DaoDien, F.DienVien, F.NuocSanXuat, F.HinhAnh
FROM PHIM F, LOAIPHIM LF
WHERE F.MaLoaiPhim = LF.MaLoaiPhim
	AND DATEDIFF(DD, @NOW, F.NgayBatDauChieu) < 0
	AND DATEDIFF(DD, @NOW, F.NgayKetThucChieu) > 0
END
GO

--3 Lay nhung phim da chieu
CREATE PROC SP_GETALLFILMSHOWED
AS
BEGIN
DECLARE @NOW DATETIME
SET @NOW = GETDATE()
SELECT DISTINCT F.MaPhim, F.TenPhim, F.NoiDung, LF.TenLoaiPhim, F.DaoDien, F.DienVien, F.NuocSanXuat, F.HinhAnh
FROM PHIM F, LOAIPHIM LF
WHERE F.MaLoaiPhim = LF.MaLoaiPhim
	AND DATEDIFF(DD, @NOW, F.NgayKetThucChieu) < 0
END
GO

--4 Lay nhung phim sap chieu
CREATE PROC SP_GETALLFILMCOMMINGSOON
AS
BEGIN
DECLARE @NOW DATETIME
SET @NOW = GETDATE()
SELECT DISTINCT F.MaPhim, F.TenPhim, F.NoiDung, LF.TenLoaiPhim, F.DaoDien, F.DienVien, F.NuocSanXuat, F.HinhAnh
FROM PHIM F, LOAIPHIM LF
WHERE F.MaLoaiPhim = LF.MaLoaiPhim
	AND DATEDIFF(DD, @NOW, F.NgayBatDauChieu) > 0
END
GO

--6 Lay phim theo ma
CREATE PROC SP_GETPHIMBYID
@MAPHIM INT
AS
BEGIN
	SELECT DISTINCT F.MaPhim, F.TenPhim, F.NoiDung, LF.TenLoaiPhim, F.DaoDien, F.DienVien, F.NuocSanXuat, F.HinhAnh
	FROM PHIM F, LOAIPHIM LF
	WHERE F.MaLoaiPhim = LF.MaLoaiPhim 
	AND F.MaPhim = @MAPHIM
END
GO

--7 Lay phong chieu theo ma
CREATE PROC SP_GET_PHONGCHIEU_BY_ID
@ID INT
AS
BEGIN
	SELECT * 
	FROM PHONGCHIEU PC
	WHERE PC.MaPhongChieu = @ID
END
GO

--8 Lay nhung suat chieu phim trong ngay va 7 ngay toi
CREATE PROC SP_GET_SUATCHIEU_TU_MAPHIM_TATCA_NGAYCHIEU
@MAPHIM INT, @THOIGIANBATDAU DATETIME
AS
BEGIN
	SELECT SC.MaSuatChieu,SC.MaPhim, SC.MaPhongChieu,SC.ThoiGianBatDau, SC.ThoiGianKetThuc, SC.SoLuongVe
	FROM SUATCHIEU SC
	WHERE SC.MaPhim = @MAPHIM
	AND DATEDIFF(DD, SC.ThoiGianBatDau, @THOIGIANBATDAU) >= 0
END
GO

--9 Lay thong tin phong chieu theo ma
CREATE PROC SP_GET_THONGTINPHONG_THEOMA
@ID INT
AS
BEGIN
	SELECT *
	FROM PHONGCHIEU
	WHERE MaPhongChieu = @ID
END
GO

--10 Lay toan bo ve
CREATE PROC SP_GET_TOANBO_VE_THEO_MAPHONG
@ID INT
AS
BEGIN
	SELECT * 
	FROM VE
END
GO

--11 Lay toan bo ve cua suat chieu
CREATE PROC SP_GET_TOANBO_VE_THEO_SUATCHIEU
@ID INT
AS
BEGIN
	SELECT * 
	FROM VE
	WHERE MaSuatChieu = @ID
END
GO

--12 Lay toan ve chua dat cho
CREATE PROC SP_GET_TOANBO_VE_CHUADAT_THEO_SUATCHIEU
@ID INT
AS
BEGIN
	SELECT * 
	FROM VE
	WHERE MaSuatChieu = @ID
	AND TinhTrang = 1
END
GO

--13 Kiem tra dang nhap
CREATE PROC SP_CHECK_LOGIN
@USERNAME NVARCHAR(50), @PASSWORD NVARCHAR(50)
AS
BEGIN
	SELECT MaTaiKhoan
	FROM TAIKHOAN
	WHERE TenDangNhap = @USERNAME
	AND MatKhau = @PASSWORD
	AND TinhTrang = 0
END

--EXEC SP_CHECK_LOGIN N'thanhit08', N'123456'

-- 14 Lay thong tin khach hang theo ma
CREATE PROC SP_GET_CUSTOMER_USERNAME
@ID INT
AS
BEGIN
	SELECT TenDangNhap
	FROM TAIKHOAN
	WHERE MaTaiKhoan = @ID
	AND TinhTrang = 0
END

--EXEC SP_GET_CUSTOMER_USERNAME 1

-- 15 Lay suat chieu theo ma
CREATE PROC SP_GET_SUATCHIEU_BYID
@ID INT
AS
BEGIN
	SELECT *
	FROM SuatChieu
	WHERE MaSuatChieu = @ID
END

--EXEC SP_GET_SUATCHIEU_BYID 1

-- 16 Lay ve theo ma
CREATE PROC SP_GET_TICKET_BYID
@ID INT
AS
BEGIN
	SELECT * 
	FROM Ve
	WHERE MaVe = @ID
END

--exec SP_GET_TICKET_BYID 1

-- 17 Lay diem thuong theo ma khach hang
CREATE PROC SP_GET_POINT_BYID
@ID INT
AS
BEGIN
	SELECT SoDiemThuong
	FROM KHACHHANG
	WHERE MaTaiKhoan = @ID
END

--EXEC SP_GET_POINT_BYID 1

-- 18 Lay ve da dat theo ma khach hang
CREATE PROC SP_GET_TICKETBOOOKED_BYID
@ID INT
AS
BEGIN
	SELECT *
	FROM VE
	WHERE MaTaiKhoan = @ID
END

--EXEC SP_GET_TICKETBOOOKED_BYID 1

-- 19 Lay danh sach loai ve
CREATE PROC SP_GET_LOAIVE
AS
BEGIN
	SELECT *
	FROM LOAIVE
END

-- 20 Lay loai ve theo ma
CREATE PROC SP_GET_LOAIVE_BYID
@ID INT
AS
BEGIN
	SELECT * 
	FROM LOAIVE
	WHERE MaLoaiVe = @ID
END

-- 21 Cap nhat loai ve
CREATE PROC SP_UPDATE_LOAIVE
@ID INT, @GIAVE FLOAT
AS
BEGIN
	UPDATE LOAIVE SET GiaVe = @GIAVE WHERE MaLoaiVe = @ID
END

--EXEC SP_UPDATE_LOAIVE 1, 75000


-- 22 Lay danh sach loai khuyen mai
CREATE PROC SP_GET_KHUYENMAI
AS
BEGIN
	SELECT *
	FROM KHUYENMAI
END

-- 23 Lay loai ve theo ma
CREATE PROC SP_GET_KHUYENMAI_BYID
@ID INT
AS
BEGIN
	SELECT * 
	FROM KHUYENMAI
	WHERE MaKhuyenMai = @ID
END

-- 24 Cap nhat loai ve
CREATE PROC SP_UPDATE_KHUYENMAI
@ID INT, @TILEGIAM FLOAT
AS
BEGIN
	UPDATE KHUYENMAI SET TiLeGiam = @TILEGIAM WHERE MaKhuyenMai = @ID
END

--EXEC SP_UPDATE_LOAIVE 1, 75000


-- 25 Lay thong tin nguoi dung theo ma
CREATE PROC GET_INFO_BYID
@ID INT
AS
BEGIN
	SELECT *
	FROM TAIKHOAN
	WHERE MaTaiKhoan = @ID
END

--EXEC GET_INFO_BYID 1

